﻿////////////////////////////////////////////////////////////////////////////////////////////////////
// file:	DBConnection\DBDatafetch.cs
//
// summary:	Implements the database datafetch class
////////////////////////////////////////////////////////////////////////////////////////////////////

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using MySql.Data.MySqlClient;

namespace Usermanagment.DBConnection
{
   ////////////////////////////////////////////////////////////////////////////////////////////////////
   /// <summary>	Database datafetch. </summary>
   ///
   /// <remarks>	Avishai, 10/09/2012. </remarks>
   ////////////////////////////////////////////////////////////////////////////////////////////////////

   public class DBDatafetch 
    {
        /// <summary>	The dbc. </summary>
        private DBConnect dbc;
		//private Boolean read = false;
		/// <summary>	The dare. </summary>
		private MySqlDataReader dare;
		/// <summary>	The command. </summary>
		private MySqlCommand cmd;
		private String playersTable, statsTable;
        ////////////////////////////////////////////////////////////////////////////////////////////////////
        /// <summary>	Constructor. </summary>
        ///
        /// <remarks>	Avishai, 10/09/2012. </remarks>
        ///
        /// <param name="dbc">	The dbc. </param>
        ////////////////////////////////////////////////////////////////////////////////////////////////////

        public DBDatafetch(DBConnect dbc)
        {
            this.dbc = dbc;
			cmd = new MySqlCommand();
			cmd.Connection = dbc.getCon();
			playersTable = dbc.playerDB;
			statsTable = dbc.playerStats;
        }

        ////////////////////////////////////////////////////////////////////////////////////////////////////
        /// <summary>	Gets all data. </summary>
        ///
        /// <remarks>	Avishai, 10/09/2012. </remarks>
        ///
        /// <returns>	all data. </returns>
        ////////////////////////////////////////////////////////////////////////////////////////////////////

        public DataTable getAllData()
        {
            cmd.CommandText = "SELECT * FROM `"+playersTable+"`";           
			MySqlDataAdapter da = new MySqlDataAdapter(cmd);
			DataTable data = new DataTable();
			da.Fill(data);
			return data;
           
        }

	   ////////////////////////////////////////////////////////////////////////////////////////////////////
	   /// <summary>	Gets all data. </summary>
	   ///
	   /// <remarks>	Avishai, 10/09/2012. </remarks>
	   ///
	   /// <param name="table">	The table. </param>
	   ///
	   /// <returns>	all data. </returns>
	   ////////////////////////////////////////////////////////////////////////////////////////////////////

	   public DataTable getAllData(String table)
		{
			cmd.CommandText = "SELECT * FROM `" + table + "`";
			MySqlDataAdapter da = new MySqlDataAdapter(cmd);
			DataTable data = new DataTable();
			da.Fill(data);
			int it = data.Columns.IndexOf("Password");
			if (it > 0)
			{
				for (int i = 0; i < data.Rows.Count; i++)
				{
					data.Rows[i][it] = "*********";
				}
			}
			return data;
		}

	   ////////////////////////////////////////////////////////////////////////////////////////////////////
	   /// <summary>	Gets all users. </summary>
	   ///
	   /// <remarks>	Avishai, 10/09/2012. </remarks>
	   ///
	   /// <returns>	all users. </returns>
	   ////////////////////////////////////////////////////////////////////////////////////////////////////

	   public DataTable getAllUsers()
	   {
		   cmd.CommandText = "SELECT `Username` FROM `" + playersTable + "`";
		   MySqlDataAdapter da = new MySqlDataAdapter(cmd);
		   DataTable data = new DataTable();
		   da.Fill(data);
		   return data;

	   }

	   ////////////////////////////////////////////////////////////////////////////////////////////////////
	   /// <summary>	Gets the tables. </summary>
	   ///
	   /// <remarks>	Avishai, 10/09/2012. </remarks>
	   ///
	   /// <returns>	The tables. </returns>
	   ////////////////////////////////////////////////////////////////////////////////////////////////////

	   public DataTable getTables()
		{
			cmd.CommandText = "SHOW Tables";
			MySqlDataAdapter da = new MySqlDataAdapter(cmd);
			DataTable dt = new DataTable();
			da.Fill(dt);
			return dt;
		}

        ////////////////////////////////////////////////////////////////////////////////////////////////////
        /// <summary>	Fetches a pass. </summary>
        ///
        /// <remarks>	Avishai, 10/09/2012. </remarks>
        ///
        /// <param name="username">	The username. </param>
        ///
        /// <returns>	The pass. </returns>
        ////////////////////////////////////////////////////////////////////////////////////////////////////

        public String fetchPass(String username)
        {
			String t = "";
			try
			{
				cmd.CommandText = "SELECT `Password` FROM `" + playersTable + "` WHERE `Username` ='" + username + "'";
				dare = cmd.ExecuteReader();
				
				while (dare.Read())
				{
					t = dare.GetString(0);
				}
				dare.Close();
			}
			catch (Exception e)
			{
				t = e.Message + " Please try again";
			}
			
			return t;

        }

        ////////////////////////////////////////////////////////////////////////////////////////////////////
        /// <summary>	Can play. </summary>
        ///
        /// <remarks>	Avishai, 10/09/2012. </remarks>
        ///
        /// <param name="username">	The username. </param>
        ///
        /// <returns>	. </returns>
        ////////////////////////////////////////////////////////////////////////////////////////////////////

        public int canPlay(String username)
        {
			cmd.CommandText = "SELECT `CanPlay` FROM `"+playersTable+"` WHERE `Username` ='" + username + "'";

			return Convert.ToInt32(cmd.ExecuteScalar());

        }

        ////////////////////////////////////////////////////////////////////////////////////////////////////
        /// <summary>	Can connect. </summary>
        ///
        /// <remarks>	Avishai, 10/09/2012. </remarks>
        ///
        /// <param name="username">	The username. </param>
        ///
        /// <returns>	. </returns>
        ////////////////////////////////////////////////////////////////////////////////////////////////////

        public int canConnect(String username)
        {
			cmd.CommandText = "SELECT `CanConnect` FROM `"+playersTable+"` WHERE `Username` ='" + username + "'";

			return Convert.ToInt32(cmd.ExecuteScalar());
        }

        ////////////////////////////////////////////////////////////////////////////////////////////////////
        /// <summary>	Gets an identifier. </summary>
        ///
        /// <remarks>	Avishai, 10/09/2012. </remarks>
        ///
        /// <param name="username">	The username. </param>
        ///
        /// <returns>	The identifier. </returns>
        ////////////////////////////////////////////////////////////////////////////////////////////////////

        public int getID(String username)
        {
			cmd.CommandText = "SELECT `ID` FROM `"+playersTable+"` WHERE `Username` ='" + username + "'";        
			return Convert.ToInt32(cmd.ExecuteScalar());
        }

        ////////////////////////////////////////////////////////////////////////////////////////////////////
        /// <summary>	Gets the chips. </summary>
        ///
        /// <remarks>	Avishai, 10/09/2012. </remarks>
        ///
        /// <param name="username">	The username. </param>
        ///
        /// <returns>	The chips. </returns>
        ////////////////////////////////////////////////////////////////////////////////////////////////////

        public int getChips(String username)
        {
			cmd.CommandText = "SELECT `Chips` FROM `"+playersTable+"` WHERE `Username` ='" + username + "'";
			return Convert.ToInt32(cmd.ExecuteScalar());

        }

		////////////////////////////////////////////////////////////////////////////////////////////////////
		/// <summary>	Gets the wins. </summary>
		///
		/// <remarks>	Avishai, 10/09/2012. </remarks>
		///
		/// <param name="username">	The username. </param>
		///
		/// <returns>	The wins. </returns>
		////////////////////////////////////////////////////////////////////////////////////////////////////

		public int getWins(String username)
		{
			cmd.CommandText = "SElECT `Wins` FROM '"+statsTable+"' WHERE `Username` ='" + username + "'";
			return Convert.ToInt32(cmd.ExecuteScalar());
		}

		////////////////////////////////////////////////////////////////////////////////////////////////////
		/// <summary>	Gets the loss. </summary>
		///
		/// <remarks>	Avishai, 10/09/2012. </remarks>
		///
		/// <param name="username">	The username. </param>
		///
		/// <returns>	The loss. </returns>
		////////////////////////////////////////////////////////////////////////////////////////////////////

		public int getLoss(String username)
		{
			cmd.CommandText = "SElECT `Loss` FROM '" + statsTable + "' WHERE `Username` ='" + username + "'";
			return Convert.ToInt32(cmd.ExecuteScalar());
		}

		////////////////////////////////////////////////////////////////////////////////////////////////////
		/// <summary>	Gets the Table size. </summary>
		///
		/// <remarks>	Avishai, 10/09/2012. </remarks>
		///
		/// <returns>	Table Size </returns>
		////////////////////////////////////////////////////////////////////////////////////////////////////

		public int gettSize()
		{
			String cmnd = "SELECT COUNT(*) FROM `"+playersTable+"`";
			MySqlCommand cmd = new MySqlCommand(cmnd, dbc.getCon());
			return int.Parse(cmd.ExecuteScalar().ToString());
		}

		
	}
}
